{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "60b7f9ad-5260-46e7-915b-11c47814c6e2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting tabula-py\n",
      "\u001b[?25l  Downloading https://files.pythonhosted.org/packages/25/aa/40404ffebbd1bb4d19ae4445f1eb63be8e12c5ab0146d0bf7e78c8d09e2e/tabula_py-2.3.0-py3-none-any.whl (12.0MB)\n",
      "\u001b[K     |████████████████████████████████| 12.0MB 8.4MB/s eta 0:00:01\n",
      "\u001b[?25hRequirement already satisfied: pandas>=0.25.3 in /home/romeokienzler/gitco/new_horizons/elyra/venv/lib/python3.8/site-packages (from tabula-py) (1.3.4)\n",
      "Requirement already satisfied: numpy in /home/romeokienzler/gitco/new_horizons/elyra/venv/lib/python3.8/site-packages (from tabula-py) (1.21.4)\n",
      "Collecting distro\n",
      "  Downloading https://files.pythonhosted.org/packages/b3/8d/a0a5c389d76f90c766e956515d34c3408a1e18f60fbaa08221d1f6b87490/distro-1.6.0-py2.py3-none-any.whl\n",
      "Requirement already satisfied: python-dateutil>=2.7.3 in /home/romeokienzler/gitco/new_horizons/elyra/venv/lib/python3.8/site-packages (from pandas>=0.25.3->tabula-py) (2.8.2)\n",
      "Requirement already satisfied: pytz>=2017.3 in /home/romeokienzler/gitco/new_horizons/elyra/venv/lib/python3.8/site-packages (from pandas>=0.25.3->tabula-py) (2021.3)\n",
      "Requirement already satisfied: six>=1.5 in /home/romeokienzler/gitco/new_horizons/elyra/venv/lib/python3.8/site-packages (from python-dateutil>=2.7.3->pandas>=0.25.3->tabula-py) (1.16.0)\n",
      "Installing collected packages: distro, tabula-py\n",
      "Successfully installed distro-1.6.0 tabula-py-2.3.0\n",
      "\u001b[33mWARNING: You are using pip version 19.3.1; however, version 21.3.1 is available.\n",
      "You should consider upgrading via the 'pip install --upgrade pip' command.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "!pip install tabula-py\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c2498ac1-01dc-475a-b1d3-bad3743747db",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Got stderr: Jan 04, 2022 2:57:41 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:41 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:41 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:42 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:42 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:42 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:43 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:43 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:43 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:44 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:44 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:44 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:45 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:45 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:45 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:46 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:46 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:46 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:47 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:47 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:47 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:48 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:48 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:48 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:49 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:49 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:49 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:50 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:50 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:50 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:51 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:51 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:51 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:52 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:52 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:53 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:53 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:53 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:54 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:54 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:54 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:55 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:55 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:55 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:56 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:56 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:56 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:56 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "Jan 04, 2022 2:57:57 PM org.apache.pdfbox.pdmodel.graphics.color.PDICCBased ensureDisplayProfile\n",
      "WARNING: ICC profile is Perceptual, ignoring, treating as Display class\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import tabula\n",
    "df = tabula.io.read_pdf('./data/sicherheitsbericht-27-12-20-bis-30-09-21.pdf', pages='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "60371120-fc3c-4ffa-b1a2-d305d9254064",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[2].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1aba4e2c-e165-4ae4-ba8c-034fa0107f13",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df = df.fillna('0')\n",
    "df = df.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "2dbab1d3-2ebc-4d44-a424-e350d3d447bf",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Impfstoff</th>\n",
       "      <th>Meldungen</th>\n",
       "      <th>schwerwiegend</th>\n",
       "      <th>Melderate</th>\n",
       "      <th>Melderate für</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Comirnaty</td>\n",
       "      <td>94.281</td>\n",
       "      <td>12.939 (13,7 %)</td>\n",
       "      <td>1,1</td>\n",
       "      <td>0,16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Spikevax</td>\n",
       "      <td>25.713</td>\n",
       "      <td>1.493 (5,8 %)</td>\n",
       "      <td>2,7</td>\n",
       "      <td>0,15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Vaxzevria</td>\n",
       "      <td>45.178</td>\n",
       "      <td>5.751 (12,7 %)</td>\n",
       "      <td>3,6</td>\n",
       "      <td>0,45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>COVID-19</td>\n",
       "      <td>6.243</td>\n",
       "      <td>560 (9,0 %)</td>\n",
       "      <td>2,0</td>\n",
       "      <td>0,18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>GESAMT</td>\n",
       "      <td>172.188</td>\n",
       "      <td>21.054 (12,2 %)</td>\n",
       "      <td>1,6</td>\n",
       "      <td>0,2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Impfstoff Meldungen    schwerwiegend Melderate Melderate für\n",
       "3   Comirnaty    94.281  12.939 (13,7 %)       1,1          0,16\n",
       "5    Spikevax    25.713    1.493 (5,8 %)       2,7          0,15\n",
       "6   Vaxzevria    45.178   5.751 (12,7 %)       3,6          0,45\n",
       "8    COVID-19     6.243      560 (9,0 %)       2,0          0,18\n",
       "14     GESAMT   172.188  21.054 (12,2 %)       1,6           0,2"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "0dfc4744-1ec4-46ee-b4e6-f57b08c681d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "pre_process = lambda s: int(s.split(' ')[0].replace(\".\",''))\n",
    "\n",
    "df['vaccine'] = df['Impfstoff']\n",
    "df['cases_severe']=df['schwerwiegend'].apply(pre_process)\n",
    "df['cases_non_severe']=df['Meldungen'].apply(pre_process)\n",
    "df['cases_non_severe']=df['cases_non_severe']-df['cases_severe']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "57de1288-179e-4ffd-b996-b374027f9684",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.drop(['Impfstoff', 'Meldungen','schwerwiegend','Melderate','Melderate für'], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "914f0cbd-2772-4c14-9d10-f07c1811894b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>vaccine</th>\n",
       "      <th>cases_severe</th>\n",
       "      <th>cases_non_severe</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Comirnaty</td>\n",
       "      <td>12939</td>\n",
       "      <td>81342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Spikevax</td>\n",
       "      <td>1493</td>\n",
       "      <td>24220</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Vaxzevria</td>\n",
       "      <td>5751</td>\n",
       "      <td>39427</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>COVID-19</td>\n",
       "      <td>560</td>\n",
       "      <td>5683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>GESAMT</td>\n",
       "      <td>21054</td>\n",
       "      <td>151134</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      vaccine  cases_severe  cases_non_severe\n",
       "3   Comirnaty         12939             81342\n",
       "5    Spikevax          1493             24220\n",
       "6   Vaxzevria          5751             39427\n",
       "8    COVID-19           560              5683\n",
       "14     GESAMT         21054            151134"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "8e78ac6e-85df-4b25-8f9d-189bfa6c95cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4daf7118-f2a4-42a4-a079-3fea2e267667",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
